package ysw.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import ysw.dao.VipDao;
import ysw.databaseconnection.DBConn;

import ysw.vo.Vip;

public class VipJdbc implements VipDao{
private  Connection conn=null;
    private PreparedStatement psmt=null;
    private ResultSet rs=null;
    public VipJdbc(){}
    public Connection getConn(){
        try{
            if(this.conn==null||this.conn.isClosed()){
                DBConn mc=new DBConn();
                this.conn=mc.getConn();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return conn;
    }
 
    public Vip addVip(Vip vip) {
       String sql="insert into  vips(?,?,?,?,?,?)";
        try{
            psmt=this.getConn().prepareStatement(sql);
    
            psmt.setInt(1,vip.getVid());
         
            psmt.setInt(2,vip.getCid());
            psmt.setString(3,vip.getCname());
            psmt.setInt(4,vip.getRank());
            psmt.setString(5,vip.getMealtype());
            psmt.setDouble(6,vip.getCredite());
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return vip;  
    }

   
    public List showVip() throws SQLException {
         String sql="select * from vips";
    List vipList=new ArrayList();
    try{
        psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
           Vip vip=new Vip();
            vip.setVid(rs.getInt("vid"));
            vip.setCid(rs.getInt("cid"));
            vip.setCname(rs.getString("cname"));
            vip.setRank(rs.getInt("rank"));
            vip.setMealtype(rs.getString("mealtype"));
            vip.setCredite(rs.getDouble("credite"));
  
        }
        return vipList;
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
             if(rs!=null){
                   rs.close();
                   rs=null;
                }
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return vipList;
    }

    public Vip showOneVip(Integer vid) throws SQLException{
      ResultSet rs=null;
    String sql="select * from vips where vid="+vid;
    Vip vip=new Vip();
    try{
      psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
        
            vip.setVid(rs.getInt("vid"));
            vip.setCid(rs.getInt("cid"));
            vip.setCname(rs.getString("cname"));
            vip.setRank(rs.getInt("rank"));
            vip.setMealtype(rs.getString("mealtype"));
            vip.setCredite(rs.getDouble("credite"));
        }
 
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            psmt.close();
              
            }catch(SQLException e){
                        e.printStackTrace();
                        }
        try{conn.close();
        
        }catch(SQLException e){
             e.printStackTrace();
        }
            }
            return vip;
    }
    public Vip updateSaveVip(Vip vip) {
        String sql="update incom set vid=?,cid=?,cname=?,rank=?,mealtype=?,credite=? where vid="+vip.getVid();
     try{
            psmt=this.getConn().prepareStatement(sql);
           psmt.setInt(1,vip.getVid());
            
            psmt.setInt(2,vip.getCid());
            psmt.setString(3,vip.getCname());
            psmt.setInt(4,vip.getRank());
            psmt.setString(5,vip.getMealtype());
            psmt.setDouble(6,vip.getCredite());
            
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
         try{
             psmt.close();
            
         }catch(SQLException e){
             e.printStackTrace();
         }
     }
     return vip;
}
    }


  


